package tsli.acm.dao;

import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;

public class AgentDebtDAO {
	 private Database db;
	    

	 public AgentDebtDAO(Database db) {
		 this.db = db;
	 }
	 
	 public List<Map<String, Object>> getAllAgentDebt() {
			String vaSql = "SELECT * "
					+ "FROM TSLI_AGENT_DEBT";
			List<Map<String, Object>> result = db.queryList(vaSql);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
	}
	 
	 public Map<String, Object> findDebtByPeNo(String paPeNo) {
//		 String vaSql = "SELECT credit, pe_no_sm, pe_no_mm, pe_no_bm " +
//				 "FROM tsli_agent_debt " +
//				 "WHERE up_level_yn ='Y' AND pe_no_owner = ?";
		 String vaSql = "SELECT Max(clos_ym) AS clos_ym, SUM(credit) AS credit, pe_no_sm, pe_no_mm, pe_no_bm " + 
				 "FROM tsli_agent_debt " +
				 "WHERE up_level_yn ='Y' AND pe_no_owner = ? " +
				 "GROUP BY pe_no_sm, pe_no_mm, pe_no_bm"; 
		 Map<String, Object> result = db.querySingle(vaSql, paPeNo);
		 if (result != null) {
			 return result;
		 } else {
			 return null;
	     }
	 }
	 
	 public Map<String, Object> findDebtByPeNo(String paPeNo, String vaQuarter, String vaYear) {
		 String[] voClosYM = new String[3];
		 if (vaQuarter.equals("1")) {
			 voClosYM[0] = vaYear + "01";
			 voClosYM[1] = vaYear + "02";
			 voClosYM[2] = vaYear + "03";
		 } else if (vaQuarter.equals("2")) {
			 voClosYM[0] = vaYear + "04";
			 voClosYM[1] = vaYear + "05";
			 voClosYM[2] = vaYear + "06";
		 } else if (vaQuarter.equals("3")) {
			 voClosYM[0] = vaYear + "07";
			 voClosYM[1] = vaYear + "08";
			 voClosYM[2] = vaYear + "09";
		 } else if (vaQuarter.equals("4")) {
			 voClosYM[0] = vaYear + "10";
			 voClosYM[1] = vaYear + "11";
			 voClosYM[2] = vaYear + "12";
		 }
		 String vaSql = "SELECT Max(clos_ym) AS clos_ym, SUM(credit) AS credit, pe_no_sm, pe_no_mm, pe_no_bm " +
				 "FROM tsli_agent_debt " + 
				 "WHERE up_level_yn ='Y' AND pe_no_owner = ? AND (clos_ym = ? OR clos_ym = ? OR clos_ym = ?) " +
				 "GROUP BY pe_no_sm, pe_no_mm, pe_no_bm";
		 Map<String, Object> result = db.querySingle(vaSql, paPeNo, voClosYM[0], voClosYM[1], voClosYM[2]);
		 if (result != null) {
			 return result;
		 } else {
			 return null;
	     }
	 }
	 
}
